/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import Connectdata.connectdatabase;
import DTO.Object.Brands.Brands;
import DTO.Object.Brands.BrandsType;
import DTO.Object.Catalogue.CatalogueType;
import DTO.Object.Catalogue.Catalogues;
import DTO.Object.OrderDetail.Orderdetail1;
import DTO.Object.OrderDetail.Orderdetails;
import DTO.Object.OrderUser.Orderuser1;
import DTO.Object.OrderUser.Orderusers;
import DTO.Object.Registers.CheckRegisterType;
import DTO.Object.Registers.CheckRegisters;
import DTO.Object.Users.UserSchema1;
import DTO.Object.Users.UserSchemas;
import DTO.ProductType;
import DTO.Products;
import java.io.File;
import java.math.BigInteger;
import java.sql.Time;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.GregorianCalendar;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Marshaller;
import javax.xml.datatype.DatatypeConfigurationException;
import javax.xml.datatype.DatatypeFactory;
import javax.xml.datatype.XMLGregorianCalendar;

/**
 *
 * @author TuPA
 */
public class marshallXML {

    public static void marshalProduct(String filePath) {

        DBUtil util = new DBUtil();
        Connection conn = null;
        StringBuilder strBuilder = new StringBuilder();
        ResultSet rs = null;
        Statement stm = null;

        try {
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
            strBuilder.append("select proID, proName, Price, Description, Image, catalogName, proType, proNumber, proFrom, noSell, brandName"
                    + " from (select proID, proName, Price, Description, Image, catalogName, proType, proNumber, proFrom, brandID, noSell "
                    + " From Product join Catalog on Product.catalogID=Catalog.catalogID) as pro"
                    + " inner join Brand"
                    + " on pro.brandID = Brand.brandID");
            stm = conn.createStatement();
            rs = stm.executeQuery(strBuilder.toString());
            Products products = new Products();
            while (rs.next()) {
//                System.out.println(rs.getString(2));
                ProductType product = new ProductType();
                product.setProID(rs.getInt(1));
                product.setProName(rs.getString(2));
                product.setPrice(rs.getInt(3));
                product.setDescription(rs.getString(4));
                product.setImage(rs.getString(5));
                product.setCatalogName(rs.getString(6));
                product.setProType(rs.getInt(7));
                product.setProNumber(rs.getInt(8));
                product.setProFrom(rs.getString(9));
                product.setNoSell(rs.getInt(10));
                product.setBrandName(rs.getString(11));
                products.getProduct().add(product);
            }
            SupportUtil SUtil = new SupportUtil();
            SUtil.marshal(filePath, products);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void marshallBrands(String filePath) {

        DBUtil util = new DBUtil();
        Connection conn = null;
        Statement stm = null;
        StringBuilder strBuilder = new StringBuilder();
        ResultSet rs = null;

        try {
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
            strBuilder.append("select * from Brand");
            stm = conn.createStatement();
            rs = stm.executeQuery(strBuilder.toString());
            Brands brands = new Brands();
            while (rs.next()) {
                BrandsType brand = new BrandsType();
                brand.setBrandID(rs.getInt(1));
                brand.setBrandName(rs.getString(2));
                brand.setBrandLink(rs.getString(3));

                brands.getBrand().add(brand);
            }
            SupportUtil SUtil = new SupportUtil();
            SUtil.marshal(filePath, brands);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void marshallCatalogues(String filePath) {

        DBUtil util = new DBUtil();
        Connection conn = null;
        Statement stm = null;
        StringBuilder strBuilder = new StringBuilder();
        ResultSet rs = null;

        try {
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
            strBuilder.append("select * from Catalog");
            stm = conn.createStatement();
            rs = stm.executeQuery(strBuilder.toString());
            Catalogues ctls = new Catalogues();
            while (rs.next()) {
//                System.out.println(rs.getString(2) );
                CatalogueType ctl = new CatalogueType();
                ctl.setCatalogID(rs.getInt(1));
                ctl.setCatalogName(rs.getString(2));
                ctl.setCatalogLink(rs.getString(3));

                ctls.getCatalogue().add(ctl);

            }
            SupportUtil SUtil = new SupportUtil();
            SUtil.marshal(filePath, ctls);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void marshallOrder(String filePathorder, String username) {
        try {
            //int tong=0;

            connectdatabase connn_orderuser = new connectdatabase();
            connn_orderuser.connect();
            connn_orderuser.connectstring("select * from OrderPayment  where orderStatus<2 and userName='" + username + "' order by orderID Desc");
            Orderusers orderusers = new Orderusers();
            //Orderdetails orderdetails = new Orderdetails();
            while (connn_orderuser.rs_connect.next()) {
                Orderuser1 orderuser1 = new Orderuser1();
                orderuser1.setOrderID(BigInteger.valueOf(connn_orderuser.rs_connect.getInt("orderID")));
                orderuser1.setUserName(username);
                String dateorder = connn_orderuser.rs_connect.getDate("orderDate") + "T" + connn_orderuser.rs_connect.getTime("orderDate");
                XMLGregorianCalendar date2 = DatatypeFactory.newInstance().newXMLGregorianCalendar(dateorder);
                orderuser1.setOrderDate(date2);
                orderuser1.setOrderStatus(connn_orderuser.rs_connect.getInt("orderStatus"));
                //int orderid = connn_orderuser.rs_connect.getInt("orderID");

//                connectdatabase connn_orderdetail = new connectdatabase();
//                connn_orderdetail.connect();
//                connn_orderdetail.connectstring("select * from OrderDetail  where orderMother="+orderid);
                //stb.append("select * from OrderDetail  where orderMother="+orderid);
                // stmt1 = con.createStatement();
                //rs1 = stmt1.executeQuery(stb.toString());
//                    while(connn_orderdetail.rs_connect.next())
//                    {
//                        Orderdetail1 orderdetail1 = new Orderdetail1();
//                        orderdetail1.setOrderMother(BigInteger.valueOf(connn_orderdetail.rs_connect.getInt("orderMother")));
//                        //orderdetail1.setProductID(BigInteger.valueOf(rs.getInt("proID")));
//                        orderdetail1.setQuantity(BigInteger.valueOf(connn_orderdetail.rs_connect.getInt("quantity")));
//                        orderdetail1.setPrice(BigInteger.valueOf(connn_orderdetail.rs_connect.getInt("priceProduct")));
//                        tong = tong+connn_orderdetail.rs_connect.getInt("quantity")*connn_orderdetail.rs_connect.getInt("priceProduct");
//
//                        connectdatabase connn_product = new connectdatabase();
//                        connn_product.connect();
//                        connn_product.connectstring("select * from Product  where proID="+connn_orderdetail.rs_connect.getInt("proID"));
//
//                    if(connn_product.rs_connect.next())
//                    {
//                        orderdetail1.setProductName(connn_product.rs_connect.getString("proName"));
//                        orderdetail1.setPicture(connn_product.rs_connect.getString("Image"));
//                    }
//
//
//                        orderdetails.getOrderdetail().add(orderdetail1);
//                    }
//                    orderdetails.setTong(tong);
//
                orderusers.getOrderuser().add(orderuser1);
            }
            JAXBContext jaxb = JAXBContext.newInstance(orderusers.getClass());
            Marshaller marorder = jaxb.createMarshaller();
            marorder.setProperty(Marshaller.JAXB_ENCODING, "UTF-8");
            marorder.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
            marorder.marshal(orderusers, new File(filePathorder));

//            JAXBContext jaxborderdetail = JAXBContext.newInstance(orderdetails.getClass());
//            Marshaller marorderdetail = jaxborderdetail.createMarshaller();
//            marorderdetail.setProperty(Marshaller.JAXB_ENCODING, "UTF-8");
//            marorderdetail.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
//            marorderdetail.marshal(orderdetails, new File(fileorderdetail));


//            UserSchemas userSchemas = new UserSchemas();
//            UserSchema1 userSchema1 = new UserSchema1();
//            connectdatabase con_user = new connectdatabase();
//            con_user.connect();
//            con_user.connectstring("select * from UserAccount  where UserName="+username);
//
//                    while(con_user.rs_connect.next())
//                    {
//                        userSchema1.setFullName(con_user.rs_connect.getString("FullName"));
//                        userSchema1.setEmail(con_user.rs_connect.getString("Mail"));
//                        userSchema1.setAddress(con_user.rs_connect.getString("Address"));
//                        userSchema1.setPhone(con_user.rs_connect.getString("Phone"));
//                        userSchemas.getUserSchema().add(userSchema1);
//                    }
//                    JAXBContext jaxbuser = JAXBContext.newInstance(userSchemas.getClass());
//            Marshaller maruser = jaxbuser.createMarshaller();
//            maruser.setProperty(Marshaller.JAXB_ENCODING, "UTF-8");
//            maruser.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
//            maruser.marshal(userSchemas, new File(fileuser));

        } catch (JAXBException ex) {
            Logger.getLogger(marshallXML.class.getName()).log(Level.SEVERE, null, ex);
        } catch (DatatypeConfigurationException ex) {
            Logger.getLogger(marshallXML.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(marshallXML.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public static void marshallRegisters(String filePath) {

        DBUtil util = new DBUtil();
        Connection conn = null;
        Statement stm = null;
        StringBuilder strBuilder = new StringBuilder();
        ResultSet rs = null;

        try {
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
            strBuilder.append("select Username, Mail, Phone from UserAccount");
            stm = conn.createStatement();
            rs = stm.executeQuery(strBuilder.toString());
            CheckRegisters crs = new CheckRegisters();

            while (rs.next()) {
//                System.out.println(rs.getString(2) );
                CheckRegisterType crt = new CheckRegisterType();
                crt.setUsername(rs.getString(1));
                crt.setEmail(rs.getString(2));
                crt.setPhone(rs.getString(3));

                crs.getCheckRegister().add(crt);
            }
            SupportUtil SUtil = new SupportUtil();
            SUtil.marshal(filePath, crs);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
